/**   
 * All rights Reserved, Designed By MiGu  
 * Copyright:    Copyright(C) 2016-2020  
 * Company       MiGu  Co., Ltd.
*/
package com.migu.spms.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.migu.spms.common.StringUtil;
import com.migu.spms.common.TimeTool;
import com.migu.spms.constant.Constant;
import com.migu.spms.constant.FlowOperateMapping;
import com.migu.spms.dao.entity.BaseLineCriteria;
import com.migu.spms.dao.entity.ChangeHistoryCriteria;
import com.migu.spms.dao.entity.FunctionRequireCriteria;
import com.migu.spms.dao.entity.FunctionRequireDO;
import com.migu.spms.dao.entity.FunctionRequirementCorrelationRequireCriteria;
import com.migu.spms.dao.entity.FunctionRequirementCorrelationRequireDO;
import com.migu.spms.dao.entity.ProjectDO;
import com.migu.spms.dao.entity.RequireAttachmentCriteria;
import com.migu.spms.dao.entity.RequireAttachmentDO;
import com.migu.spms.dao.entity.RequireTypeTreeCriteria;
import com.migu.spms.dao.entity.RequireTypeTreeDO;
import com.migu.spms.dao.entity.RequireUserCriteria;
import com.migu.spms.dao.entity.RequireUserDO;
import com.migu.spms.dao.entity.SubParentRequireCriteria;
import com.migu.spms.dao.entity.SubParentRequireDO;
import com.migu.spms.dao.entity.UserInfoCriteria;
import com.migu.spms.dao.entity.UserInfoDO;
import com.migu.spms.dao.inf.IFunctionRequireDO;
import com.migu.spms.dao.inf.IFunctionRequirementCorrelationRequireDO;
import com.migu.spms.dao.inf.IProjectDO;
import com.migu.spms.dao.inf.IRequireAttachmentDO;
import com.migu.spms.dao.inf.IRequireTypeTreeDO;
import com.migu.spms.dao.inf.IRequireUserDO;
import com.migu.spms.dao.inf.ISubParentRequireDO;
import com.migu.spms.dao.inf.IUserInfoDO;
import com.migu.spms.service.data.User;
import com.migu.spms.service.inf.IChangeHistoryService;
import com.migu.spms.service.inf.IFunctionFlowService;
import com.migu.spms.service.inf.IRequireExcelService;
import com.migu.spms.service.inf.ISequenceCreateService;
import com.migu.spms.utils.CodeCreateUtil;
import com.migu.spms.utils.ExcelUtil;
import com.migu.spms.utils.exception.ServiceException;
import com.migu.spms.webapi.data.ExcelRequest;

/**   
 * RequireExcelServiceImpl类
 * 项目名称:  SPMS功能需求
 * 包名称:    com.migu.spms.service.impl   
 * 类名称:    RequireExcelServiceImpl.java
 * 类描述:    
 * 创建人:    wcr   
 * 创建时间:  2017年7月4日 上午10:38:00   
 */
@Transactional
@Service("requireExcelServiceImpl")
public class RequireExcelServiceImpl implements IRequireExcelService {

    @Resource(name = "IFunctionRequireDO")
    IFunctionRequireDO functionRequireDO;

    @Resource(name = "IRequireTypeTreeDO")
    IRequireTypeTreeDO requireTypeTreeDO;

    @Resource(name = "ISubParentRequireDO")
    ISubParentRequireDO subParentRequireDO;

    @Resource(name = "IProjectDO")
    IProjectDO projectDO;

    @Resource(name = "IRequireUserDO")
    private IRequireUserDO requireUserDO;

    @Resource(name = "IRequireAttachmentDO")
    private IRequireAttachmentDO fileAttachDO;
    
    @Resource(name = "IUserInfoDO")
    private IUserInfoDO userInfoDO;
    
    @Resource(name = "changeHistoryServiceImpl")
    private IChangeHistoryService changeHistoryService;
    
    @Resource(name = "functionFlowServiceImpl")
    private IFunctionFlowService functionFlowService;
    
    @Resource(name = "sequenceService")
    private ISequenceCreateService sequenceService;
    
    @Resource(name = "IFunctionRequirementCorrelationRequireDO")
    private IFunctionRequirementCorrelationRequireDO functionRequirementCorrelationRequireDO;
    
    @Override
    public void exportRequireInfo(ExcelRequest msg, HttpServletRequest req, HttpServletResponse resp) throws Exception {
        if (StringUtil.isEmpty(msg.getProjectId())) {
            throw new ServiceException("项目ID不能为空！");
        }
        List<String> titles = getExcelTitles();
        XSSFWorkbook workbook = new XSSFWorkbook();
        Map<String, Object> model = new HashMap<String, Object>();
        model.put("titles", titles);

        FunctionRequireCriteria criteria = new FunctionRequireCriteria();
        criteria.setProjectId(Long.valueOf(msg.getProjectId()));
        List<FunctionRequireDO> requireList = functionRequireDO.selectExcelRequireList(criteria);
        List<SubParentRequireDO> subList = subParentRequireDO.selectByCriteria(new SubParentRequireCriteria());

        List<RequireTypeTreeDO> treeList = requireTypeTreeDO.selectRequireTypeTreeList(new RequireTypeTreeCriteria());
        Map<Long, String> mapLeve = getTypeTreeLeve(treeList);

        List<FunctionRequireDO> fistFb = new ArrayList<FunctionRequireDO>();
        Map<Long, List<FunctionRequireDO>> mapFb = new HashMap<Long, List<FunctionRequireDO>>();
        for (FunctionRequireDO fb : requireList) {
            int number = 0;
            for (SubParentRequireDO sb : subList) {
                if (fb.getId().equals(sb.getSubRequireId())) {
                    number += 1;
                }
            }
            if (number == 0) {
                fistFb.add(fb);
            }
        }

        List<FunctionRequireDO> fbAllByLeve = new ArrayList<FunctionRequireDO>();
        for (FunctionRequireDO nextFb : fistFb) {
            fbAllByLeve.add(nextFb);
            getSubFunctionRequireMap(fbAllByLeve, subList, requireList, nextFb);
        }
        model.put("varList", fbAllByLeve);
        buildExcelDocument(model, workbook, req, resp, fistFb, fbAllByLeve, mapLeve);
    }

    private void buildExcelDocument(Map<String, Object> model, XSSFWorkbook workbook, HttpServletRequest request,
            HttpServletResponse response, List<FunctionRequireDO> fistFb, List<FunctionRequireDO> fbAllByLeve,
            Map<Long, String> mapLeve) throws Exception {
        XSSFSheet sheet;
        XSSFCell cell;
        response.reset();
        // response.setContentType("multipart/form-data;charset=utf-8");
        response.setContentType("application/octet-stream;charset=utf-8");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=pcExcel.xlsx");
        sheet = workbook.createSheet("sheet1");

        List<String> titles = (List<String>) model.get("titles");
        int len = titles.size();
        XSSFCellStyle headerStyle = workbook.createCellStyle(); // 标题样式
        headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        headerStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        XSSFFont headerFont = workbook.createFont(); // 标题字体
        headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        headerFont.setFontHeightInPoints((short) 11);
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        headerStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
        headerStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
        headerStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
        headerStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
        short width = 20, height = 25 * 20;
        sheet.setDefaultColumnWidth(width);

        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < len; i++) { // 设置标题
            String title = titles.get(i);
            cell = row.createCell(i);
            cell.setCellValue(title);
            cell.setCellStyle(headerStyle);
        }
        sheet.getRow(0).setHeight(height);

        XSSFCellStyle contentStyle = workbook.createCellStyle(); // 内容样式
        contentStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        contentStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
        contentStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
        contentStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
        contentStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
        List<FunctionRequireDO> varList = (List<FunctionRequireDO>) model.get("varList");

        int varCount = varList.size();
        for (int i = 0; i < varCount; i++) {
            XSSFRow rowH = sheet.createRow(i + 1);
            FunctionRequireDO fb = varList.get(i);
            for (int j = 0; j < len; j++) {
                cell = rowH.createCell(j);
                switch (j) {
                case 0:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(mapLeve.get(fb.getRequireTypeId()));
                    break;
                case 1:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(fb.getRequireTypeName());
                    break;
                case 2:
                    cell.setCellStyle(contentStyle);
                    ProjectDO projectDo = projectDO
                            .selectByPrimaryKey(Integer.parseInt(Long.toString(fb.getProjectId())));
                    cell.setCellValue(null == projectDo ? "" : projectDo.getName());
                    break;
                case 3:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(fb.getRequireId());
                    break;
                case 4:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(fb.getRequireName());
                    break;
                case 5:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(FlowOperateMapping.getChineseStatus(fb.getStatus()));
                    break;
                case 6:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(null == fb.getCurrentHandlerName() ? "" : fb.getCurrentHandlerName());
                    break;
                case 7:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(null == fb.getBaselineVersion() ? "" : fb.getBaselineVersion());
                    break;
                case 8:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(fb.getCreatorName());
                    break;
                case 9:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(TimeTool.DateToString(fb.getCreateTime()));
                    break;
                case 10:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue((null == fb.getParentIdExcel()) ? "" : Long.toString(fb.getParentIdExcel()));
                    break;
                case 11:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(TimeTool.DateToString(fb.getExpectFinishTime()));
                    break;
                case 12:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(null == fb.getPriorityRank() ? "" : fb.getPriorityRank());
                    break;
                case 13:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(StringUtil.isEmpty(fb.getRelativeBusinessRequireId()) ? ""
                            : fb.getRelativeBusinessRequireId());
                    break;
                case 14:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(
                            null == fb.getRelativeBusinessRequireName() ? "" : fb.getRelativeBusinessRequireName());
                    break;
                case 15:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(null == fb.getEstimateWorkload() ? "" : fb.getEstimateWorkload());
                    break;
                case 16:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(null == fb.getPlanOnlineVersion() ? "" : fb.getPlanOnlineVersion());
                    break;
                case 17:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(null == fb.getActualVersion() ? "" : fb.getActualVersion());
                    break;
                case 18:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(null == fb.getDevelopResponsibleName() ? "" : fb.getDevelopResponsibleName());
                    break;
                case 19:
                    cell.setCellStyle(contentStyle);
                    RequireUserCriteria requireUserCriteria = new RequireUserCriteria();
                    requireUserCriteria.setFunctionRequireId(fb.getId());
                    requireUserCriteria.setRoleId(Constant.Role.DEVELOPER);
                    List<RequireUserDO> developers = requireUserDO.selectByCriteria(requireUserCriteria);
                    String userNames = "";
                    if (null != developers && developers.size() > 0) {
                        for (RequireUserDO requireUserDO : developers) {
                            userNames += requireUserDO.getUserName() + ",";
                        }
                    }
                    cell.setCellValue(
                            StringUtil.isEmpty(userNames) ? "" : userNames.substring(0, userNames.lastIndexOf(",")));
                    break;
                case 20:
                    cell.setCellStyle(contentStyle);
                    cell.setCellValue(fb.getTestResponsibleName());
                    break;
                case 21:
                    cell.setCellStyle(contentStyle);
                    RequireUserCriteria requireUserCriteriat = new RequireUserCriteria();
                    requireUserCriteriat.setFunctionRequireId(fb.getId());
                    requireUserCriteriat.setRoleId(Constant.Role.TESTER);
                    List<RequireUserDO> testers = requireUserDO.selectByCriteria(requireUserCriteriat);
                    String testerstr = "";
                    if (null != testers && testers.size() > 0) {
                        for (RequireUserDO requireUserDO : testers) {
                            testerstr += requireUserDO.getUserName() + ",";
                        }
                    }
                    cell.setCellValue(
                            StringUtil.isEmpty(testerstr) ? "" : testerstr.substring(0, testerstr.lastIndexOf(",")));
                    break;
                case 22:
                    cell.setCellStyle(contentStyle);
                    RequireAttachmentCriteria reAttch = new RequireAttachmentCriteria();
                    reAttch.setFunctionRequireId(fb.getId());
                    List<RequireAttachmentDO> attchList = fileAttachDO.selectRequireAttachment(reAttch);
                    String attchNames = "";
                    if (null != attchList && attchList.size() > 0) {
                        for (RequireAttachmentDO requireAttachmentDO : attchList) {
                            attchNames += requireAttachmentDO.getAttachmentName() + ",";
                        }
                    }
                    cell.setCellValue(
                            StringUtil.isEmpty(attchNames) ? "" : attchNames.substring(0, attchNames.lastIndexOf(",")));
                    break;
                case 23:
                    cell.setCellStyle(contentStyle);
                    FunctionRequirementCorrelationRequireCriteria correlation = new FunctionRequirementCorrelationRequireCriteria();
                    correlation.setFunctionRequireId(Long.toString(fb.getId()));
                    List<FunctionRequirementCorrelationRequireDO> selectByCriteria = 
                            functionRequirementCorrelationRequireDO.selectByCriteria(correlation);
                    String iden = "";
                    if(null != selectByCriteria && selectByCriteria.size()>0){
                        for (FunctionRequirementCorrelationRequireDO fdo : selectByCriteria) {
                            FunctionRequireDO funRequire = functionRequireDO.
                                    selectByPrimaryKey(Long.valueOf(fdo.getCorrelationRequireId()));
                            if(null!=funRequire&&funRequire.getRequireId()!=null&&!StringUtil.isEmpty(funRequire.getRequireId())){
                            	iden += funRequire.getRequireId() + ",";
                            }
                        }
                    }
                    cell.setCellValue(StringUtil.isEmpty(iden)?"":iden.substring(0, iden.lastIndexOf(",")));
                    break;
                default:
                    break;
                }
            }
        }

        ServletOutputStream os = response.getOutputStream();
        workbook.write(os);
        os.flush();
        os.close();
    }

    private void getSubFunctionRequireMap(List<FunctionRequireDO> fbAllByLeve, List<SubParentRequireDO> subList,
            List<FunctionRequireDO> requireList, FunctionRequireDO fb) {
        List<FunctionRequireDO> fbList = new ArrayList<FunctionRequireDO>();
        for (SubParentRequireDO sb : subList) {
            if (fb.getId().equals(sb.getParentRequireId())) {
                for (FunctionRequireDO fr : requireList) {
                    if (sb.getSubRequireId().equals(fr.getId())) {
                        fr.setParentIdExcel(Long.valueOf(fb.getRequireId()));
                        fbList.add(fr);
                        fbAllByLeve.add(fr);
                    }
                }
            }
        }
        if (fbList.size() > 0) {
            for (FunctionRequireDO functionRequireDO : fbList) {
                getSubFunctionRequireMap(fbAllByLeve, subList, requireList, functionRequireDO);
            }
        }
    }

    private Map<Long, String> getTypeTreeLeve(List<RequireTypeTreeDO> treeList) {
        Map<Long, String> mapLeve = new HashMap<Long, String>();
        int number = 0;
        for (RequireTypeTreeDO requireTypeTreeDO : treeList) {
            if (requireTypeTreeDO.getPid().equals(0L)) {
                mapLeve.put(requireTypeTreeDO.getId(), number + "级");
                getSonInfo(requireTypeTreeDO, number + 1, treeList, mapLeve);
            }
        }
        return mapLeve;
    }

    private void getSonInfo(RequireTypeTreeDO ft, int number, List<RequireTypeTreeDO> treeList,
            Map<Long, String> mapLeve) {
        for (RequireTypeTreeDO requireTypeTreeDO : treeList) {
            if (requireTypeTreeDO.getPid().equals(ft.getId())) {
                mapLeve.put(requireTypeTreeDO.getId(), number + "级");
                getSonInfo(requireTypeTreeDO, number + 1, treeList, mapLeve);
            }
        }
    }

    /**
     * 设置Excel表头
     * @param applyType
     * @return
     * @exception
     * @throws
     */
    private List<String> getExcelTitles() {
        List<String> titles = new ArrayList<String>();
        titles.add("需求分类层级关系");
        titles.add("需求分类");
        titles.add("项目组名称");
        titles.add("需求编号");
        titles.add("需求标题");
        titles.add("当前状态");
        titles.add("当前处理人");
        titles.add("基线版本号");
        titles.add("创建人");
        titles.add("创建时间");
        titles.add("上一级父需求编号");
        titles.add("计划完成时间");
        titles.add("优先级");
        titles.add("关联业务需求编号");
        titles.add("关联业务需求名称");
        titles.add("预估工作量");
        titles.add("计划上线版本");
        titles.add("实现版本");
        titles.add("开发负责人");
        titles.add("开发人员");
        titles.add("测试负责人");
        titles.add("测试人员");
        titles.add("附件名称");
        titles.add("关联功能需求编号");
        return titles;
    }

    private String statusToStr(String status) {
        String str = "";
        if (Constant.flowStatus.DEVELOPING.equals(status)) {
            str = "开发中";
        } else if (Constant.flowStatus.NEWING.equals(status)) {
            str = "新建";
        } else if (Constant.flowStatus.TRANFER_TEST.equals(status)) {
            str = "转测试";
        } else if (Constant.flowStatus.TESTING.equals(status)) {
            str = "测试中";
        } else if (Constant.flowStatus.IMPLEMENTED.equals(status)) {
            str = "已实施";
        } else if (Constant.flowStatus.REOPEN.equals(status)) {
            str = "重新打开";
        }
        return str;
    }

    private static int dataStartRow = 1;
    private int totalRows; // sheet中总行数
    private static int totalCells; // 每一行总单元格数
    
    @Override
    public void readExcel(ExcelRequest msg, MultipartFile file) throws Exception {
        List<FunctionRequireCriteria> readExcelToObj = (List<FunctionRequireCriteria>) readExcelToObj(file);
        Map<String,String> map = new HashMap<String, String>();
        List<SubParentRequireCriteria> subList = new ArrayList<SubParentRequireCriteria>();
        List<RequireUserCriteria> requireUser = new ArrayList<RequireUserCriteria>();
        
        for (FunctionRequireCriteria functionRequireCriteria : readExcelToObj) {
            functionRequireDO.insertSelective(functionRequireCriteria);
            
            ChangeHistoryCriteria history = new ChangeHistoryCriteria();
            history.setCreateId(Long.valueOf(functionRequireCriteria.getCreatorId()));
            history.setCreateName(functionRequireCriteria.getCreatorName());
            history.setCreateTime(new Date());
            history.setIsDetails("0");
            history.setIsPermit("0");
            history.setIsBaseLine(Integer.toString(functionRequireCriteria.getIsBaseLine()));
            history.setFunctionRequireId(functionRequireCriteria.getId());
            history.setBaselineVersion(functionRequireCriteria.getBaselineVersion());
            if(functionRequireCriteria.getStatus().equals(Constant.flowStatus.NEWING)){
                history.setRemark("新建需求");
                
                BaseLineCriteria criteria2 = new BaseLineCriteria();
                criteria2.setRequireKey(functionRequireCriteria.getId());
                //复制需求，启动新流程
                functionFlowService.startProcessFlow(criteria2.getRequireKey(), new User());
            }else if(functionRequireCriteria.getStatus().equals(Constant.flowStatus.IMPLEMENTED)){
                history.setRemark("已实现");
            }
            if(!StringUtil.isEmpty(functionRequireCriteria.getIdentifi())){
                FunctionRequirementCorrelationRequireCriteria rec = new FunctionRequirementCorrelationRequireCriteria();
                rec.setFunctionRequireId(Long.toString(functionRequireCriteria.getId()));
                rec.setIdentifi("0");
                FunctionRequireCriteria ff = new FunctionRequireCriteria();
                String[] splitIden = functionRequireCriteria.getIdentifi().split(",");
                if(splitIden.length > 0){
                    for (String strIden : splitIden) {
                        ff.setRequireId(strIden);
                        FunctionRequireDO selectByCriteria = functionRequireDO.selectByIdentifi(ff);
                        if(null == selectByCriteria){
                            throw new ServiceException("关联功能需求有误！！！");
                        }else{
                            rec.setCorrelationRequireId(Long.toString(selectByCriteria.getId()));
                            functionRequirementCorrelationRequireDO.insertSelective(rec);
                        }    
                    }
                }
                
            }
            changeHistoryService.inesertChangeHistoryCommon(history, null, null);
        }
        for (FunctionRequireCriteria functionRequireCriteria : readExcelToObj) {
            map.put(functionRequireCriteria.getExcelID(), Long.toString(functionRequireCriteria.getId()));
            if(null != functionRequireCriteria.getRequireUserList() && 
                    functionRequireCriteria.getRequireUserList().size() > 0){
                for (RequireUserCriteria requireUserCriteria : functionRequireCriteria.getRequireUserList()) {
                    requireUserCriteria.setFunctionRequireId(functionRequireCriteria.getId());
                    requireUser.add(requireUserCriteria);
                }
            }
            
        }
        if(requireUser.size()>0){
            requireUserDO.batchInsert(requireUser);
        }
        for (FunctionRequireCriteria functionRequireCriteria : readExcelToObj) {
            if(!StringUtil.isEmpty(functionRequireCriteria.getExcelParentID())){
                SubParentRequireCriteria sub = new SubParentRequireCriteria();
                sub.setParentRequireId(Long.valueOf(map.get(functionRequireCriteria.getExcelParentID())));
                sub.setSubRequireId(Long.valueOf(map.get(functionRequireCriteria.getExcelID())));
                subList.add(sub);
            }
        }
        if(subList.size()>0){
            subParentRequireDO.batchInsert(subList);
        }
    }
    
    private List<?> readExcelToObj(MultipartFile file) throws Exception {
        if (file == null || ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim())) {
            return null;
        } else {
            String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
            if (!ExcelUtil.EMPTY.equals(postfix)) {
                if (ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(file);
                } else if (ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {               
                    return readXlsx(file);
                } else {
                    return null;
                }
            }
        }
        return null;
    }

    
    private List<FunctionRequireCriteria> readXlsx(MultipartFile file) throws Exception{
        List<FunctionRequireCriteria> list = new ArrayList<FunctionRequireCriteria>(); 
        // IO流读取文件
        InputStream input = null;
        XSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new XSSFWorkbook(input);
            // 读取sheet(页)

            XSSFSheet xssfSheet = wb.getSheetAt(0);

            totalRows = xssfSheet.getLastRowNum();
            // 读取Row,从第二行开始
            for (int rowNum = dataStartRow; rowNum <= totalRows; rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                FunctionRequireCriteria fun = new FunctionRequireCriteria();

                if (xssfRow != null) {
                    rowList = new ArrayList<String>();
                    if(xssfRow.getLastCellNum() > 0){
                        totalCells = xssfRow.getLastCellNum();
                    }else{
                        totalCells = totalRows;
                    }
                    List<RequireUserCriteria> requireUser = new ArrayList<RequireUserCriteria>();
                    // 读取列，从第一列开始
                    for (int c = 0; c < totalCells; c++) {
                        XSSFCell cell = xssfRow.getCell(c);
                        String trim = ExcelUtil.getXValue(cell).trim();
                        
                        switch (c) {
                        case 0:
                            fun.setExcelID(trim);
                            break;
                        case 1:
                            
                            break;
                        case 2:    
                            RequireTypeTreeCriteria tree = new RequireTypeTreeCriteria();
                            tree.setName(trim);
                            List<RequireTypeTreeDO> selectRequireTypeTreeList = requireTypeTreeDO.selectRequireTypeTreeList(tree);
                            if(null == selectRequireTypeTreeList || selectRequireTypeTreeList.size() == 0){
                               throw new ServiceException(trim + "：分类不存在！"); 
                            }else{
                                fun.setRequireTypeId(selectRequireTypeTreeList.get(0).getId());
                            }
                            fun.setRequireTypeName(trim);
                            
                            break;
                        case 3:
                            //项目ID
                            ProjectDO pro = projectDO.selectProjectByName(trim);
                            if(null == pro){
                                throw new ServiceException(trim + "：项目组不存在！"); 
                            }
                            fun.setProjectId(Long.valueOf(pro.getPid()));
                            RequireTypeTreeCriteria typeTree = new RequireTypeTreeCriteria();
                            typeTree.setName(fun.getRequireTypeName());
                            typeTree.setProjectId(Long.valueOf(pro.getPid()));
                            List<RequireTypeTreeDO> RequireTypeTreeList = requireTypeTreeDO.selectRequireTypeTreeList(typeTree);
                            fun.setRequireTypeId(RequireTypeTreeList.get(0).getId());
                            break;
                        case 4:
                            fun.setRequireName(trim);
                            break;
                        case 5:
                            fun.setDetails(trim);
                            break;
                        case 6:
                            if(trim.equals("已实现")){
                                fun.setStatus(Constant.flowStatus.IMPLEMENTED);
                                fun.setIsBaseLine(1);
                            }else{
                                fun.setStatus(Constant.flowStatus.NEWING);
                                fun.setIsBaseLine(0);
                            }
                            
                            break;
                        case 7:
                            //当前处理人
                            if(!StringUtil.isEmpty(trim)){
                                UserInfoCriteria user = new UserInfoCriteria();
                                user.setUsername_py(trim);
                                UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                fun.setCurrentHandlerId(Long.valueOf(userInfo.getUid()));
                            }
                            break;
                        case 8:
                            fun.setCurrentHandlerName(trim);
                            break;
                        case 9:
                            fun.setBaselineVersion(trim);
                            break;
                        case 10:
                            //创建人ID
                            if(!StringUtil.isEmpty(trim)){
                                UserInfoCriteria user = new UserInfoCriteria();
                                user.setUsername_py(trim);
                                UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                fun.setCreatorId(Long.valueOf(userInfo.getUid()));;
                            }
                            break;
                        case 11:
                            fun.setCreatorName(trim);
                            break;
                        case 12:
                            fun.setCreateTime(StringUtil.isEmpty(trim)?null:TimeTool.StrToDate(trim));
                            break;
                        case 13:
                            if(StringUtil.isEmpty(trim)){
                                fun.setIsSubRequire(0);
                            }else{
                                fun.setIsSubRequire(1);
                            }
                            fun.setExcelParentID(StringUtil.isEmpty(trim)?"":trim);
                            break;
                        case 14:
                            fun.setExpectFinishTime(StringUtil.isEmpty(trim)?null:TimeTool.StrToDate(trim));
                            break;
                        case 15:
                            fun.setPriorityRank(trim);
                            break;
                        case 16:
                            fun.setRelativeBusinessRequireId(trim);
                            break;
                        case 17:
                            fun.setRelativeBusinessRequireName(trim);
                            break;
                        case 18:
                            fun.setEstimateWorkload(trim);
                            break;
                        case 19:
                            fun.setPlanOnlineVersion(trim);
                            break;
                        case 20:
                            fun.setActualVersion(trim);
                            break;
                        case 21:
                            //开发负责人ID
                            if(!StringUtil.isEmpty(trim)){
                                UserInfoCriteria user = new UserInfoCriteria();
                                user.setUsername_py(trim);
                                UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                fun.setDevelopResponsibleId(Long.valueOf(userInfo.getUid()));
                            }
                            break;
                        case 22:
                            fun.setDevelopResponsibleName(trim);
                            break;
                        case 23:
                            //开发人员ID
                            if(!StringUtil.isEmpty(trim)){
                                String[] split = trim.split(",");
                                for (String str : split) {
                                    UserInfoCriteria user = new UserInfoCriteria();
                                    user.setUsername_py(str);
                                    UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                    fun.setDevelopResponsibleId(Long.valueOf(userInfo.getUid()));
                                    RequireUserCriteria ruser = new RequireUserCriteria();
                                    ruser.setRoleId(Constant.Role.DEVELOPER);
                                    ruser.setUserId(Long.valueOf(userInfo.getUid()));
                                    ruser.setUserName(userInfo.getUsername());
                                    requireUser.add(ruser);
                                }
                                
                            }
                            break;
                        case 25:
                            //测试负责人ID
                            if(!StringUtil.isEmpty(trim)){
                                UserInfoCriteria user = new UserInfoCriteria();
                                user.setUsername_py(trim);
                                UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                fun.setTestResponsibleId(Long.valueOf(userInfo.getUid()));
                            }
                            break;
                        case 26:
                            fun.setTestResponsibleName(trim);
                            break;
                        case 27:
                            //测试人员ID
                            if(!StringUtil.isEmpty(trim)){
                                String[] split = trim.split(",");
                                for (String str : split) {
                                    UserInfoCriteria user = new UserInfoCriteria();
                                    user.setUsername_py(str);
                                    UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                    fun.setDevelopResponsibleId(Long.valueOf(userInfo.getUid()));
                                    RequireUserCriteria ruser = new RequireUserCriteria();
                                    ruser.setRoleId(Constant.Role.TESTER);
                                    ruser.setUserId(Long.valueOf(userInfo.getUid()));
                                    ruser.setUserName(userInfo.getUsername());
                                    requireUser.add(ruser);
                                }
                            }
                            break;
                        case 29:
                            fun.setIdentifi(trim);
                            break;
                        default:
                            break;
                        }
                    }
                    fun.setRequireUserList(requireUser);
                    fun.setRequireId(CodeCreateUtil.createRequireId(fun.getProjectId(), sequenceService.getRequireNumber()));
                    list.add(fun);
                }
            }
            
            return list;
        } catch (IOException e) {
            throw new ServiceException("Excel文件格式有误！");
        } finally {
            try {
                input.close();
            } catch (IOException e) {
                throw new ServiceException("Excel文件格式有误！");
            }
        }
    }
    
    private List<FunctionRequireCriteria> readXls(MultipartFile file) throws Exception{
        List<FunctionRequireCriteria> list = new ArrayList<FunctionRequireCriteria>(); 
        // IO流读取文件
        InputStream input = null;
        HSSFWorkbook wb = null;
        try {
            input = file.getInputStream();
            // 创建文档
            wb = new HSSFWorkbook(input);
            HSSFSheet hssfSheet = wb.getSheetAt(0);
            totalRows = hssfSheet.getLastRowNum();

            // 读取Row,从第二行开始
            for (int rowNum = dataStartRow; rowNum <= totalRows; rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                FunctionRequireCriteria fun = new FunctionRequireCriteria();
                if (hssfRow != null) {
                    if(hssfRow.getLastCellNum() > 0){
                        totalCells = hssfRow.getLastCellNum();
                    }else{
                        totalCells = totalRows;
                    }
                    List<RequireUserCriteria> requireUser = new ArrayList<RequireUserCriteria>();
                    // 读取列，从第一列开始
                    for (short c = 0; c < totalCells; c++) {
                        HSSFCell cell = hssfRow.getCell(c);
                        /*
                         * if (cell == null) { obj.add(ExcelUtil.EMPTY); continue; }
                         */
                        String trim = ExcelUtil.getHValue(cell).trim();
                        
                        switch (c) {
                        case 0:
                            fun.setExcelID(trim);
                            break;
                        case 1:
                            
                            break;
                        case 2:    
                            RequireTypeTreeCriteria tree = new RequireTypeTreeCriteria();
                            tree.setName(trim);
                            List<RequireTypeTreeDO> selectRequireTypeTreeList = requireTypeTreeDO.selectRequireTypeTreeList(tree);
                            if(null == selectRequireTypeTreeList || selectRequireTypeTreeList.size() == 0){
                               throw new ServiceException(trim + "：分类不存在！"); 
                            }else{
                                fun.setRequireTypeId(selectRequireTypeTreeList.get(0).getId());
                            }
                            fun.setRequireTypeName(trim);
                            
                            break;
                        case 3:
                            //项目ID
                            ProjectDO pro = projectDO.selectProjectByName(trim);
                            if(null == pro){
                                throw new ServiceException(trim + "：项目组不存在！"); 
                            }
                            fun.setProjectId(Long.valueOf(pro.getPid()));
                            break;
                        case 4:
                            fun.setRequireName(trim);
                            break;
                        case 5:
                            fun.setDetails(trim);
                            break;
                        case 6:
                            if(trim.equals("已实现")){
                                fun.setStatus(Constant.flowStatus.IMPLEMENTED);
                                fun.setIsBaseLine(1);
                            }else{
                                fun.setStatus(Constant.flowStatus.NEWING);
                                fun.setIsBaseLine(0);
                            }
                            
                            break;
                        case 7:
                            //当前处理人
                            if(!StringUtil.isEmpty(trim)){
                                UserInfoCriteria user = new UserInfoCriteria();
                                user.setUsername_py(trim);
                                UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                fun.setCurrentHandlerId(Long.valueOf(userInfo.getUid()));
                            }
                            break;
                        case 8:
                            fun.setCurrentHandlerName(trim);
                            break;
                        case 9:
                            fun.setBaselineVersion(trim);
                            break;
                        case 10:
                            //创建人ID
                            if(!StringUtil.isEmpty(trim)){
                                UserInfoCriteria user = new UserInfoCriteria();
                                user.setUsername_py(trim);
                                UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                fun.setCreatorId(Long.valueOf(userInfo.getUid()));;
                            }
                            break;
                        case 11:
                            fun.setCreatorName(trim);
                            break;
                        case 12:
                            fun.setCreateTime(StringUtil.isEmpty(trim)?null:TimeTool.StrToDate(trim));
                            break;
                        case 13:
                            if(StringUtil.isEmpty(trim)){
                                fun.setIsSubRequire(0);
                            }else{
                                fun.setIsSubRequire(1);
                            }
                            fun.setExcelParentID(StringUtil.isEmpty(trim)?"":trim);
                            break;
                        case 14:
                            fun.setExpectFinishTime(StringUtil.isEmpty(trim)?null:TimeTool.StrToDate(trim));
                            break;
                        case 15:
                            fun.setPriorityRank(trim);
                            break;
                        case 16:
                            fun.setRelativeBusinessRequireId(trim);
                            break;
                        case 17:
                            fun.setRelativeBusinessRequireName(trim);
                            break;
                        case 18:
                            fun.setEstimateWorkload(trim);
                            break;
                        case 19:
                            fun.setPlanOnlineVersion(trim);
                            break;
                        case 20:
                            fun.setActualVersion(trim);
                            break;
                        case 21:
                            //开发负责人ID
                            if(!StringUtil.isEmpty(trim)){
                                UserInfoCriteria user = new UserInfoCriteria();
                                user.setUsername_py(trim);
                                UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                fun.setDevelopResponsibleId(Long.valueOf(userInfo.getUid()));
                            }
                            break;
                        case 22:
                            fun.setDevelopResponsibleName(trim);
                            break;
                        case 23:
                            //开发人员ID
                            if(!StringUtil.isEmpty(trim)){
                                String[] split = trim.split(",");
                                for (String str : split) {
                                    UserInfoCriteria user = new UserInfoCriteria();
                                    user.setUsername_py(trim);
                                    UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                    fun.setDevelopResponsibleId(Long.valueOf(userInfo.getUid()));
                                    RequireUserCriteria ruser = new RequireUserCriteria();
                                    ruser.setRoleId(Constant.Role.DEVELOPER);
                                    ruser.setUserId(Long.valueOf(userInfo.getUid()));
                                    ruser.setUserName(userInfo.getUsername());
                                    requireUser.add(ruser);
                                }
                                
                            }
                            break;
                        case 25:
                            //测试负责人ID
                            if(!StringUtil.isEmpty(trim)){
                                UserInfoCriteria user = new UserInfoCriteria();
                                user.setUsername_py(trim);
                                UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                fun.setTestResponsibleId(Long.valueOf(userInfo.getUid()));
                            }
                            break;
                        case 26:
                            fun.setTestResponsibleName(trim);
                            break;
                        case 27:
                            //测试人员ID
                            if(!StringUtil.isEmpty(trim)){
                                String[] split = trim.split(",");
                                for (String str : split) {
                                    UserInfoCriteria user = new UserInfoCriteria();
                                    user.setUsername_py(trim);
                                    UserInfoDO userInfo = userInfoDO.queryUserInfoByCriteria(user);
                                    fun.setDevelopResponsibleId(Long.valueOf(userInfo.getUid()));
                                    RequireUserCriteria ruser = new RequireUserCriteria();
                                    ruser.setRoleId(Constant.Role.TESTER);
                                    ruser.setUserId(Long.valueOf(userInfo.getUid()));
                                    ruser.setUserName(userInfo.getUsername());
                                    requireUser.add(ruser);
                                }
                            }
                            break;
                        case 29:
                            fun.setIdentifi(trim);
                            break;
                        default:
                            break;
                        }
                        
                    }
                    fun.setRequireUserList(requireUser);
                    fun.setRequireId(CodeCreateUtil.createRequireId(fun.getProjectId(), sequenceService.getRequireNumber()));
                    list.add(fun);
                }
            }

            return list;
        } catch (IOException e) {
            throw new ServiceException("Excel文件格式有误！");
        } finally {
            try {
                input.close();
            } catch (IOException e) {
                throw new ServiceException("Excel文件格式有误！");
            }
        }
    }

}
